package com.xh.excel;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * 类说明
 *
 * @author yangyangang
 * @version V1.0 创建时间: 2019/6/4 16:00 Copyright 2019 by WiteMedia
 */
@Slf4j
public class MillionExportToExcelUtil {
    /**
     * 数据库连接操作
     */
    public Connection getConnection() throws Exception {
        // 使用jdbc链接数据库
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        String url = "jdbc:mysql://localhost:3306/test_excel?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT";
        String username = "root";
        String password = "123456";
        // 获取数据库连接
        Connection conn = DriverManager.getConnection(url, username, password);
        return conn;
    }

    public List<Object> getTestData(String fileds, String tableName, int limits) throws Exception {
        Connection conn = this.getConnection();
        Statement stmt = conn.createStatement();
        String sql = "select "+fileds+" from " + tableName + " limit " + limits;
        System.out.println(sql);
        ResultSet rs = stmt.executeQuery(sql); // 获取执行结果
        return convertList(rs);
    }

    private List convertList(ResultSet rs) throws SQLException{
        List list = new ArrayList();
        while (rs.next()) {
            User user = new User();
            user.setId(rs.getInt(1));
            user.setUser_id(rs.getString(2));
//            user.setVote_id(rs.getInt(3));
//            user.setGroup_id(rs.getInt(4));
            user.setCreate_time(rs.getString(3));
            list.add(user);
        }
        return list;
    }

    /**
     * @return boolean
     * @Name WriteExcel
     * @Description 执行导出Excel操作
     * @Author wen
     * @Date 2019/6/4
     */
    public boolean WriteExcel(String excelFileName, String[] titles, List<Object> dataList, String[] filds, int count) {
        // 内存中只创建100个对象，写临时文件，当超过100条，就将内存中不用的对象释放。
        SXSSFWorkbook wb = new SXSSFWorkbook(100);
        Sheet sheet = null; // 工作表对象
        Row nRow = null; // 行对象
        Cell nCell = null; // 列对象
        try {
            long startTime = System.currentTimeMillis();
            log.info("==================开始执行时间:{}", startTime / 1000 + "m");
            int rowNo = 0; // 总行号
            int pageRowNo = 0; // 页行号

            for (int i = 0; i < dataList.size(); i++) {
                if (rowNo % count == 0) {
                    log.info("==================当前sheet页为:" + rowNo / count);
                    sheet = wb.createSheet("我的第" + (rowNo / count + 1) + "个工作簿"); // 建立新的sheet对象
                    sheet = wb.getSheetAt(rowNo / count); // 动态指定当前的工作表
                    pageRowNo = 1; // 每当新建了工作表就将当前工作表的行号重置为1
                    //定义表头
                    nRow = sheet.createRow(0);
                    for (int j = 0; j < titles.length; j++) {
                        Cell cel0 = nRow.createCell(j);
                        cel0.setCellValue(titles[j]);
                    }
                }
                rowNo++;
                nRow = sheet.createRow(pageRowNo++); // 新建行对象
                // 打印每行，每行有6列数据 rsmd.getColumnCount()==6 --- 列属性的个数

                this.handleData(dataList.get(i), nRow, nCell, Arrays.asList(filds));

                if (rowNo % 10000 == 0) {
                    log.info("==================" + rowNo);
                }
            }
            long finishedTime = System.currentTimeMillis(); // 处理完成时间
            log.info("==================数据读取完成耗时 : " + (finishedTime - startTime) / 1000 + "m");
            FileOutputStream fOut = new FileOutputStream(excelFileName);//将数据写入Excel
            wb.write(fOut);
            fOut.flush(); // 刷新缓冲区
            fOut.close();
            long stopTime = System.currentTimeMillis(); // 写文件时间
            log.info("==================数据写入Excel表格中耗时 : " + (stopTime - startTime) / 1000 + "m");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    protected void handleData(Object o, Row nRow, Cell nCell, List<String> filds) throws IllegalAccessException {
        Class<?> clazz = o.getClass();
        Field[] declaredFields = clazz.getDeclaredFields();
        int num = 0;
        for (int i = 0; i < declaredFields.length; i++) {
            Field declaredField = declaredFields[i];
            declaredField.setAccessible(true);
            if(filds.contains(declaredField.getName())){
                nCell = nRow.createCell(num++);
                nCell.setCellValue(declaredField.get(o) == null ? "" : declaredField.get(o).toString());
            }
        }
    }

    //测试方法
    public static void main(String[] args) throws Exception {
        MillionExportToExcelUtil bdeo = new MillionExportToExcelUtil();
        String filds = "id,user_id,create_time";
        String tableName = "vote_record_memory";

        String[] titles = "主键,用户id,创建时间".split(",");
        List<Object> testData = bdeo.getTestData(filds, tableName, 100000);
        bdeo.WriteExcel("E:/test1.xlsx", titles, testData, filds.split(","), 10000);
    }

}
